'''
@by hql 20221201
python3 go.py  src_mysql_sql_file        dest_dm_sql_file
e.g:
python3 go.py  /home/hql/mysql_table.sql /home/hql/out.dm.sql

Since then , need use Mysql sql parser , can be all Ok,eg.:

'''
import os
import re
import sys

def rp_str(str1):
    '''
    1. remove `
    2. remove USING BTREE.*$
    3. remove CHARACTER SET utf8mb4
    4. remove USING HASH
    5. remove COLLATE utf8xxx
    3. replace xxxint(xx) unsigned TO bigint
    4. replace bit(1) To int
    '''
    for rs in ("'0000-00-00 00:00:00'",):
        str1=str1.replace('0000-00-00 00:00:00','1900-01-01 00:00:00')
    for rs in ('`current`','`comment`','`ref`','`over`','`datetime`'):
        str1=str1.replace(rs,'"'+rs.upper()+'" ')
    for rs in ('`','USING HASH'):
        str1=str1.replace(rs,'')
        #str1=str1.replace('　',' ')#remove blank char
    str1=re.sub(' [a-z]*int\([0-9]+\) [u]*[n]*[s]*[i]*[g]*[n]*[e]*[d]*[ ]*[z]*[e]*[r]*[o]*[f]*[i]*[l]*[l]*',' bigint ',str1)
    str1=re.sub(r'(varchar\([0-9]+)(\))', r'\1 char\2',str1)
    str1=re.sub('CHARACTER SET utf8[a-z0-4]*', '',str1)
    str1=re.sub(' COLLATE utf8[_a-z0-9]*', ' ',str1)
    str1=re.sub(' bit\(1\)', ' int',str1).replace("b'","'")
    ##  KEY `IDX_USER_ID` (`user_id`) USING BTREE COMMENT '用户id索引'
    str1=re.sub(' USING BTREE.*$', ' ',str1)
    ##print((str1,str1[:-1]))
    #return str1[:-1]#replace -1 for one more row : \n
    return str1.strip('\n')#replace -1 for one more row : \n

file_out=open(sys.argv[2],'w',encoding='UTF-8')
def print(rs):
    # re modify back for #,  create_time datetime NOT NDEFAULT CURRENT_TIMESTAMP
    if re.search('NOT CURRENT_TIMESTAMP',rs):
        rs=rs.replace('NOT CURRENT_TIMESTAMP','NOT NULL DEFAULT CURRENT_TIMESTAMP')
    if re.search('DEFAULT NUL$',rs):
        rs=rs.replace('DEFAULT NUL','DEFAULT NULL')
    file_out.write(rs+'\n')
    file_out.flush()

file=open(sys.argv[1],'r',encoding='UTF-8')

comments=''
indexs=''
autoPk=0
NeedTrig=0
for rs in file.readlines():#[0:500]:
    rs=rp_str(rs)
    in_0=0
    if rs[:2] in ('--','/*','\n'):
        ###print(('skip:',rs,rs[:2]))
        continue
    if 'ENGINE=' in rs:
        print(');\n')
    else:
        if 'AUTO_INCREMENT' in rs:
            rs=rs.replace('AUTO_INCREMENT','primary key AUTO_INCREMENT')
            autoPk=1
        if 'ON UPDATE CURRENT_TIMESTAMP' in rs:
            #rs=rs.replace('DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP','DEFAULT CURRENT_TIMESTAMP').replace('ON UPDATE CURRENT_TIMESTAMP','DEFAULT CURRENT_TIMESTAMP').replace('DEFAULT NULL DEFAULT CURRENT_TIMESTAMP','DEFAULT CURRENT_TIMESTAMP') #TODO need check
            rs=re.sub(r'[C]*[U]*[R]*[R]*[E]*[N]*[T]*[_]*[T]*[I]*[M]*[E]*[S]*[T]*[A]*[M]*[P]*[N]*[U]*[L]*[L]*[\(]*([0-9]*)[\)]* ON UPDATE CURRENT_TIMESTAMP[\(0-9\)]*',r'CURRENT_TIMESTAMP(\1)',rs) #DONE \1 need stranfer to trig?
            #print(rs+'-->')
            scal=re.sub(r'.*[C]*[U]*[R]*[R]*[E]*[N]*[T]*[_]*[T]*[I]*[M]*[E]*[S]*[T]*[A]*[M]*[P]*[N]*[U]*[L]*[L]*[\(]*([0-9]*)[\)]*.*',r'\1',rs)
            #print('<--'+scal)
            indexs=indexs+'''
CREATE OR REPLACE TRIGGER TRG_UPD_'''+table+'''
 BEFORE UPDATE ON '''+table+'''
 FOR EACH ROW
 BEGIN
     :new.'''+rs.strip().split()[0]+'''=CURRENT_TIMESTAMP('''+scal+''');
 END;
/
'''
        if rs[:20] == 'DROP TABLE IF EXISTS':
            autoPk=0
            in_0 = 1
            table=''
            if len(comments)>0:
                print(comments)
            comments=''
            if len(indexs)>0:
                print(indexs)
            indexs=''
            print(rs.strip(';')+' cascade;')

        else:
            ##print('-->'+rs.strip(' ')+'<--')
            strs1=rs.split()
            if rs[:13] == 'CREATE TABLE ':
                comma=0
                table=strs1[2]
                print(rs)
            elif rs.strip(' ')=='': #skip null rows
                continue
            else:
                #for primary-key\key\constraint
                #print('-->'+rs)
                # why? \u4e00-\u9fa5 for chinesse colname-at-pk
                if re.search(r'''PRIMARY KEY (\([, _a-zA-Z\u4e00-\u9fa5]+\))''',rs):
                    if autoPk==1:
                        continue
                    ##print('gogogo'*23)
                    ##print(rs+'--pk')
                    indexs=indexs+re.sub(r'''PRIMARY KEY (\([, _a-zA-Z\u4e00-\u9fa5]+\))''','Alter table '+table+r' add primary key \1',rs.strip(' |,')) +';\n'
                    continue
                if re.search(r'''UNIQUE KEY [_a-zA-Z0-9]+ \(([, _a-zA-Z]+)\)''',rs):
                    ##print('gogogo'*23)
                    ##print(rs+'--ukey')
                    cols=re.sub(r'''.*UNIQUE KEY [_a-zA-Z0-9]+ \(([, _a-zA-Z]+)\).*''',r'\1',rs)
                    indexs=indexs+re.sub(r'''UNIQUE KEY [_a-zA-Z0-9]+ \(([, _a-zA-Z]+)\)''','Create unique index idx_'+table+'_'+cols.replace(',','_')+' on '+table+r'(\1)',rs.strip(' |,'))+';\n'
                    continue
                #if re.search(r'''KEY [_a-zA-Z0-9]+ \(([, _a-z]+)\)''',rs):
                #    ##print('gogogo'*23)
                #    ##print(rs+'--key')
                #    cols=re.sub(r'''.*KEY [_a-zA-Z0-9]+ \(([, _a-z]+)\).*''',r'\1',rs)
                #    indexs=indexs+re.sub(r'''KEY [_a-zA-Z0-9]+ \(([, _a-z]+)\)''','Create index idx_'+table+'_'+cols.replace(',','_')+' on '+table+r'(\1)',rs.strip(' |,'))+';\n'
                #    continue
                if re.search(r'''KEY [_a-zA-Z0-9]+ \(([, _a-zA-Z\(\)0-9]+)\)''',rs): # KEY `idx_certify_id` (`certify_id`(255)) USING BTREE,
                    ##print('gogogo'*23)
                    ##print(rs+'--key')
                    cols=re.sub(r'''.*KEY [_a-zA-Z0-9]+ \(([, _a-zA-Z\(\)0-9]+)\).*''',r'\1',rs)
                    cols=re.sub('\([0-9]+\)','',cols)
                    indexs=indexs+re.sub(r'''KEY [_a-zA-Z0-9]+ \(([, _a-zA-Z\(\)0-9]+)\)''','Create index idx_'+table+'_'+cols.replace(',','_')+' on '+table+'('+cols+')',rs.strip(' |,'))+';\n'
                    continue
                if re.search(r'''CONSTRAINT [a-zA-Z0-9]+ FOREIGN KEY \(([, _a-zA-Z]+)\) REFERENCES ([_a-zA-Z0-9]+) \(([, _a-zA-Z]+)\)''',rs):
                    ##print('gogogo'*23)
                    ##print(rs+'--fk')
                    cols=re.sub(r'''.*CONSTRAINT [a-zA-Z0-9]+ FOREIGN KEY \(([, _a-zA-Z]+)\) REFERENCES ([_a-zA-Z0-9]+) \(([, _a-zA-Z]+)\).*''',r'\1',rs)
                    indexs=indexs+re.sub(r'''CONSTRAINT [a-zA-Z0-9]+ FOREIGN KEY \(([, _a-zA-Z]+)\) REFERENCES ([_a-zA-Z0-9]+) \(([, _a-zA-Z]+)\)''','Alter table '+table+' add constraint FK_'+table+'_'+cols.replace(',','_')+' foreign key '+r'(\1)'+' references '+r'\2'+r'(\3)',rs.strip(' |,'))+';\n'
                    continue
                    
                #for others
                strs1=rs.split(' COMMENT ')
                if len(strs1)==1:
                    strs1=strs1[0][:-1].split(' COMMENT ') ##remove end ,
                
                colname=rs.strip().split(' ')[0]
                if len(strs1[0])>0:
                    ##print('---'+strs1[0]+' -->' +str(len(strs1[0])) )
                    if comma:
                        print(','+strs1[0])
                    else:
                        print(strs1[0])
                        comma=1
                        '''
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '记录id',
  `primary_id` varchar(64) DEFAULT NULL COMMENT 'ALTER TABLE `xx`.`xx` \r\nADD COLUMN `primary_id` varchar(64) NULL COMMENT ''xxID :xxxxxx及自定义页
面 的 唯一id (xxxxprimaryId)'' AFTER `xxx`;',  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1421 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
'''
                #for comment include comment
                if len(strs1)>2:
                    for rs in range(len(strs1)-2):
                        strs1[1]=strs1[1]+' COMMENT '+strs1[rs+2]
                
                if len(strs1)>1:
                    ##print(strs1[1],strs1[1][:-1])
                    comments=comments+'comment on column '+table+'.'+colname+' is '+strs1[1].strip(',')+';\n'

#last one print
print(comments)
print(indexs)
print('exit')
